package com.wingsfrog.puzzle.modules.dao;

import com.wingsfrog.puzzle.controller.dto.response.PlayListDto;
import com.wingsfrog.puzzle.controller.dto.response.RecordDto;
import com.wingsfrog.puzzle.controller.dto.response.UserResDto;
import com.wingsfrog.puzzle.modules.entity.Picture;
import com.wingsfrog.puzzle.modules.entity.PuzzleRecord;
import com.wingsfrog.puzzle.modules.entity.User;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface PuzzleRecordDao extends CrudRepository<PuzzleRecord,Integer>{
    @Query("select new com.wingsfrog.puzzle.controller.dto.response.RecordDto(" +
            "u.avatar, u.nickname, r.step, r.time) " +
            "from PuzzleRecord r, User u " +
            "where r.userId=u.id and r.pictureId=?1 " +
            "order by r.step, r.time")
    public List<RecordDto> findScoreBoard(Integer picId);

    @Query("select r " +
            "from PuzzleRecord r " +
            "where r.userId=?1 and r.pictureId=?2")
    public PuzzleRecord getRecord(Integer userId, Integer picId);

    @Query("select p " +
            "from Picture p, PuzzleRecord r " +
            "where r.userId=?1 and r.pictureId=p.id " +
            "order by r.updatedAt desc")
    public List<Picture> getPlayList(Integer userId);

    @Query(value="SELECT p.id, p.path, r.hardness" +
            " FROM picture p, puzzle_record r" +
            " WHERE p.id = r.picture_id AND r.user_id = ?1" +
            " GROUP BY r.picture_id, r.user_id" +
            " HAVING r.hardness >=(" +
            "     SELECT max(tt.hardness)" +
            "     FROM puzzle_record tt" +
            "     WHERE r.picture_id = tt.picture_id" +
            "     GROUP BY tt.picture_id" +
            ")" +
            "", nativeQuery = true)
    public Object[] getOccupyList(Integer userId);

    @Query("select new com.wingsfrog.puzzle.controller.dto.response.UserResDto(u.id, u.avatar, u.nickname, r.hardness)" +
            " from PuzzleRecord r, User u" +
            " where r.userId = u.id and r.pictureId = ?1" +
            " group by r.userId" +
            " order by r.updatedAt desc")
    public List<UserResDto> getUserList(Integer picId);
}
